package conexion;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Conexion {
    //propiedades

    private String nombre;
    private String servidor;
    private String driver = "oracle.jdbc.driver.OracleDriver";
    private String user;
    private String password;
    private String db = "";
    private Integer puerto;
    private String servicio;
    //objetos Auxiliares (Delegados)
    private Connection conexion = null;

    public Conexion() {
    }

    public Conexion(String nombre, String servidor, String driver, String user, String password, String db, Integer puerto, String servicio) {
        this.nombre = nombre;
        this.servidor = servidor;
        this.driver = driver;
        this.user = user;
        this.password = password;
        this.db = db;
        this.puerto = puerto;
        this.servicio = servicio;
    }

    public Conexion(String nombre, String servidor, String driver, String user, String password, Integer puerto, String servicio) {
        this.nombre = nombre;
        this.servidor = servidor;
        this.driver = driver;
        this.user = user;
        this.password = password;
        this.puerto = puerto;
        this.servicio = servicio;

    }

    public Connection getConexionOracle() throws SQLException {
        try {
            Class.forName(this.getDriver());
            conexion = DriverManager.getConnection("jdbc:oracle:thin:@"
                    + this.getServidor() + ":" + this.getPuerto() + ":"
                    + this.getServicio(), this.getUser(), this.getPassword());
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Conexion.class.getName()).log(Level.SEVERE, null, ex);
        }
        return conexion;
    }

    public Connection getConexionSqlServer() throws SQLException {
        try {
            Class.forName(this.getDriver());
            conexion = DriverManager.getConnection("jdbc:sqlserver://"
                    + this.getServidor() + ":" + this.getPuerto() + ";" + this.getDb(), this.getUser(), this.getPassword());
            System.out.println("jdbc:sqlserver://"
                    + this.getServidor() + ":" + this.getPuerto() + ";");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(Conexion.class.getName()).log(Level.SEVERE, null, ex);
        }
        return conexion;
    }

    public List<String> getMetaData() throws SQLException {
        List<String> result = new ArrayList<String>(); 
        CallableStatement st = conexion.prepareCall("{call sp_helpdb}");
        ResultSet rs =st.executeQuery();
        while (rs.next()) {
            result.add(rs.getString(1));
            
        }
        
        return result;
    }
    
    public void getSquema(String sq, Boolean vw, Boolean cons) throws SQLException { 
        String proc = "{call GET_TABLES_MD('HR',TRUE,TRUE)}";
        CallableStatement st = conexion.prepareCall(proc);
//        st.setString(1, sq);
//        st.setBoolean(2, vw);
//        st.setBoolean(3, cons);
        st.execute();
    }

    public Connection getConexionOtro() {

        return null;
    }

    /**
     * @return the driver
     */
    public String getDriver() {
        return driver;
    }

    /**
     * @param driver the driver to set
     */
    public void setDriver(String driver) {
        this.driver = driver;
    }

    /**
     * @return the user
     */
    public String getUser() {
        return user;
    }

    /**
     * @param user the user to set
     */
    public void setUser(String user) {
        this.user = user;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return password;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.password = password;
    }

    /**
     * @return the db
     */
    public String getDb() {

        return db.equals("") ? "" : "databaseName = " + db;
    }

    /**
     * @param db the db to set
     */
    public void setDb(String db) {
        this.db = db;
    }

    /**
     * @return the servidor
     */
    public String getServidor() {
        return servidor;
    }

    /**
     * @param servidor the servidor to set
     */
    public void setServidor(String servidor) {
        this.servidor = servidor;
    }

    /**
     * @return the puerto
     */
    public Integer getPuerto() {
        return puerto;
    }

    /**
     * @param puerto the puerto to set
     */
    public void setPuerto(Integer puerto) {
        this.puerto = puerto;
    }

    /**
     * @return the servicio
     */
    public String getServicio() {
        return servicio;
    }

    /**
     * @param servicio the servicio to set
     */
    public void setServicio(String servicio) {
        this.servicio = servicio;
    }

    public static void main(String[] args) {
        try {
            Conexion con = new Conexion("Henrys con", "10.20.94.110", "oracle.jdbc.driver.OracleDriver", "HR", "henry", 1521, "orcl");
            ResultSet rs = con.getConexionOracle().createStatement().executeQuery("select * from employees");
            while (rs.next()) {
                System.out.println("" + rs.getInt(1));

            }

        } catch (SQLException ex) {
            System.out.println("Excepcion" + ex);
        }
    }

    /**
     * @return the nombre
     */
    public String getNombre() {
        return nombre;
    }

    /**
     * @param nombre the nombre to set
     */
    public void setNombre(String nombre) {
        this.nombre = nombre;
    }
}
